//-------------------------------------------------------------
// \project PluginExample3
// \file ModuleQuery.java
// \brief 查询模块
// \author 王庆飞
// \date 2016-9-12
// \attention
// Copyright(c) 超图软件股份有限公司 气象事业部
// All Rights Reserved
// \version 1.0
//-------------------------------------------------------------

package com.mg.plugin.agrometeorology.model;

import com.mg.model.Model;
import com.mg.model.Module;
import com.mg.model.Param;
import com.mg.model.Port;
import com.mg.pluginexample2.Model2;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class ModuleInsertData extends Module {
    public ModuleInsertData(int nID) {
        super(nID);
        m_alInputPort.add(new Port(this, Port.Type.Unknown));
    }

    public void Destroy() {
        super.Destroy();
    }

    public String GetGroupName() {
        return "JDBC";
    }

    public String GetName() {
        return "插入数据";
    }

    public String GetDescription() {
        return "插入数据";
    }

    public boolean OnAttach(Port portFrom, Port portTo) {
        try {
            int i = FindPort(portFrom, false);
            if (i != -1)
                return true;
            i = FindPort(portTo, true);
            if (i != 0) {
                return false;
            }

            if (portFrom.GetType() != Port.Type.CSV) {
                if (portFrom.GetType() != portTo.GetType()) {
                    return false;
                }
            }


            ArrayList<Param> alParam = new ArrayList<Param>();
            portFrom.GetModule().GetOutputParam(portFrom, alParam);
            if (alParam.size() != 1) {
                return false;
            }

//            if (alParam.get(0).m_objValue != null && (!(alParam.get(0).m_objValue instanceof ArrayList) || !(alParam.get(0).m_objValue instanceof LinkedList))) {
//                m_model.OutputLog(Model.LogLevel.Info, "ERROR!");
//                return false;
//            }
            if (alParam.get(0).m_objValue instanceof LinkedList) {
                LinkedList<String> tmp = (LinkedList<String>) alParam.get(0).m_objValue;
                m_list.addAll(tmp);
            } else {
                m_list = (List<String>) alParam.get(0).m_objValue;
            }
            portTo.SetName(portFrom.GetName());
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    //用于 参数->XML 等
    public int GetParam(ArrayList<Param> alParam) {
        try {
            int nOffset = super.GetParam(alParam);
            //-------------------------------------------------------------------------------------------------------------------
            alParam.add(new Param("Driver", m_strDriver, "Driver", "", ""));
            alParam.add(new Param("Url", m_strUrl, "Url", "", ""));
            alParam.add(new Param("User", m_strUser, "用户名", "", ""));
            alParam.add(new Param("Password", m_strPassword, "密码", "", ""));
            alParam.add(new Param("table", m_table, "table", "插入表", ""));
            alParam.add(new Param("sign", m_sign, "sign", "分割符", ""));
            alParam.add(new Param("key", m_mianKey, "主键字段", "多个,隔开", ""));
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return alParam.size();
    }

    //用于 XML->参数 等
    public int SetParam(final ArrayList<Param> alParam) {
        int i = super.SetParam(alParam);

        try {
            m_strDriver = (String) alParam.get(i++).m_objValue;
            m_strUrl = (String) alParam.get(i++).m_objValue;
            m_strUser = (String) alParam.get(i++).m_objValue;
            m_strPassword = (String) alParam.get(i++).m_objValue;
            m_table = (String) alParam.get(i++).m_objValue;
            m_sign = (String) alParam.get(i++).m_objValue;
            m_mianKey = (String) alParam.get(i++).m_objValue;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return i;
    }

    public boolean Execute() {
        try {
            Model2 model = ((Model2) m_model);

            m_model.OutputLog(Model.LogLevel.Info, "Data Size:" + m_list.size());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Class.forName(m_strDriver);
            m_connection = DriverManager.getConnection(m_strUrl, m_strUser, m_strPassword);
            String column = m_list.get(0).replace(" ", "");
            String[] columns = column.split(",");
            String[] typeli = m_list.get(1).replace(" ", "").split(",");
            String sql = "insert into " + m_table + " (" + column + ") VALUES (";
            String strUpdateSql = "update " + m_table + " set ";
            String[] keys = m_mianKey.split(",");

            Statement stmt = m_connection.createStatement();
            for (int i = 2; i < m_list.size(); i++) {
                StringBuilder sbUpdate = new StringBuilder();
                sbUpdate.append(strUpdateSql);

                String insertSqlString = sql;
                String col = m_list.get(i);
                String[] ataticValueStrings = col.split(m_sign, typeli.length);
                String strCondition = "";
                for (int j = 0; j < typeli.length; j++) {

                    if (j != 0) {
                        insertSqlString += ",";
                        if (!ataticValueStrings[j].isEmpty())
                            sbUpdate.append(",");
                    }
                    if (typeli[j].equalsIgnoreCase("int") || typeli[j].equalsIgnoreCase("long")) {
                        insertSqlString += ataticValueStrings[j];
                        if (!ataticValueStrings[j].isEmpty()) {
                            sbUpdate.append(columns[j]);
                            sbUpdate.append("=" + ataticValueStrings[j]);
                        }
                    } else if (typeli[j].equalsIgnoreCase("date") || typeli[j].equalsIgnoreCase("TIME")) {
                        if (m_strDriver.contains("postgres"))
                            insertSqlString += "to_timestamp('";
                        else
                            insertSqlString += "to_date('";

                        insertSqlString += ataticValueStrings[j];
                        insertSqlString += "','yyyy-MM-dd hh24:mi:ss')";

                        sbUpdate.append(columns[j]);
                        if (m_strDriver.contains("postgres"))
                            sbUpdate.append("=" + " to_timestamp('");
                        else
                            sbUpdate.append("=" + " to_date('");
                        sbUpdate.append(ataticValueStrings[j]);
                        sbUpdate.append("','yyyy-MM-dd hh24:mi:ss')");

                    } else {
                        insertSqlString += "'";
                        insertSqlString += ataticValueStrings[j];
                        insertSqlString += "'";
                        if (!ataticValueStrings[j].isEmpty()) {
                            sbUpdate.append(columns[j]);
                            sbUpdate.append("='" + ataticValueStrings[j] + "'");
                        }
                    }
                    //主键
                    for (int num = 0; num < keys.length; num++) {
                        if (columns[j].equalsIgnoreCase(keys[num].trim())) {
                            String filed = "";
                            if (typeli[j].equalsIgnoreCase("int") || typeli[j].equalsIgnoreCase("long"))
                                filed = ataticValueStrings[j];
                            else
                                filed = "'" + ataticValueStrings[j] + "'";
                            if (!strCondition.isEmpty())
                                strCondition += "and ";
                            strCondition += columns[j] + "=" + filed;
                            break;
                        }
                    }
                }
                insertSqlString += ")";
                sbUpdate.append("where " + strCondition);
                if (stmt == null) {
                    m_model.OutputLog(Model.LogLevel.Info, "STMT IS NULL");
                    return false;
                }

                boolean hasResultSet;
                try {
                    hasResultSet = stmt.execute(insertSqlString);
                    if (!hasResultSet) {
                        stmt.execute(sbUpdate.toString());
                        m_model.OutputLog(Model.LogLevel.Info, "更新成功:hasResultSet" + sbUpdate);
                    } else {
                        m_model.OutputLog(Model.LogLevel.Info, "hasResultSet IS NULL" + insertSqlString);
                    }
                } catch (Exception e) {
                    m_model.OutputLog(Model.LogLevel.Info, "更新成功:" + sbUpdate);
                    stmt.execute(sbUpdate.toString());
                }
            }
            OnParamChanged();
            this.m_model.OutputLog(Model.LogLevel.Info, "SUCCESS");
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                m_connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        this.m_model.OutputLog(Model.LogLevel.Info, "Exception");
        return false;
    }

    public int GetOutputParam(Port port, ArrayList<Param> alParam) {
        int i = FindPort(port, false);
        if (i == -1)
            return 0;
        if (i == 0) {
            alParam.add(new Param("ResultSet", m_rs));
        }

        return alParam.size();
    }

    Connection m_connection = null;
    String m_strDriver = "";
    String m_strUrl = "";
    String m_strUser = "";
    String m_strPassword = "";
    String m_table = "";
    String m_sign = "";
    String m_mianKey = "";
    List<String> m_list = new ArrayList<String>();
    ResultSet m_rs;
}
